
from flask import Blueprint, render_template
import pymysql
bp = Blueprint('hp', __name__, url_prefix='/hp')



@bp.get('/')
def index():
    db = Mysql()
    types = db.getType()
    type_data = []
    for type in types:
        type_data.append({'type': type[1], 'number': type[0]})

    res_price = db.getRe_price()
    re_price_data = []
    for re_price in res_price:
        renovation = re_price[1]  # 装修程度
        avg_price = re_price[0]  # 平均成交价格
        re_price_data.append({'renovation': renovation, 'avg_price': avg_price})


    res_number = db.getRe_number()
    re_number_data = []
    for re_number in res_number:
        re_number_data.append({'renovation':re_number[1],'number':re_number[0]})

    floor_numbers = db.get_floor_number()
    floor_number_data = []
    for floor_number in floor_numbers:
        floor_number_data.append({'floor':floor_number[1],'number':floor_number[0]})

    floor_prices = db.get_floor_price()
    floor_price_data = []
    for floor_price in floor_prices:
        floor_price_data.append({'floor': floor_price[1], 'avg_price': floor_price[0]})


    area_prices = db.get_area_price()
    area_price_data = []
    for area_price in area_prices:
        area_price_data.append({'area': area_price[1], 'price': area_price[0]})


    return render_template('gz/by.html', type_data=type_data,re_price_data=re_price_data,
                           re_number_data=re_number_data,floor_number_data=floor_number_data,floor_price_data=floor_price_data,
                           area_price_data=area_price_data)



class Mysql(object):
    def __init__(self):
        try:
            self.conn = pymysql.connect(host='localhost',user='root',password='123456',database='ershoufang',charset="utf8")
            self.cursor = self.conn.cursor()  # 用来获得python执行Mysql命令的方法（游标操作）
            print("连接数据库成功")
        except:
            print("连接失败")

    def getType(self):
        sql= "select number,type from gz_hp_type_number"
        self.cursor.execute(sql)
        types = self.cursor.fetchall()  #接收全部的返回结果行
        return types

    def getRe_price(self):
        sql = "select avg_transaction_price,renovation from gz_hp_re_number"
        self.cursor.execute(sql)
        res_price = self.cursor.fetchall()
        return res_price

    def getRe_number(self):
        sql = "select number,renovation from gz_hp_re_number"
        self.cursor.execute(sql)
        res_number = self.cursor.fetchall()
        return res_number

    def get_floor_number(self):
        sql = "select number,floor from gz_hp_floor_number_price"
        self.cursor.execute(sql)
        floor_number = self.cursor.fetchall()
        return floor_number

    def get_floor_price(self):
        sql = "select avg_transaction_price,floor from gz_hp_floor_number_price"
        self.cursor.execute(sql)
        floor_price = self.cursor.fetchall()
        return floor_price


    def get_area_price(self):
        sql = "select price,area from gz_hp_area_price order by area"
        self.cursor.execute(sql)
        area_price = self.cursor.fetchall()
        return area_price













